package cn.hwadee.muying.commons.utils;

import cn.hwadee.muying.dto.Worker;
import cn.hwadee.muying.enums.Education;
import cn.hwadee.muying.enums.Level;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @描述: Excel文件工具类
 * @作者: Jing
 * @日期: 2017-06-26 10:30
 * @版本: v1.0
 */
public class ExcelUtils {

    public static <T> List<T> excelToMapList(
            InputStream in,
            Map<String, String> fieldMap,
            List<String> uniqueFields,
            Map<String,String> notNullFields
    ) throws RuntimeException{

        //定义要返回的list
        List<T> resultList = new ArrayList<T>();

        try {

            //根据Excel数据源创建WorkBook
            Workbook wb = WorkbookFactory.create(in);
            //获取工作表
            for(int n = 0; n < wb.getNumberOfSheets();n++){
                Sheet sheet = wb.getSheetAt(n);

                //获取工作表的有效行数
                int realRows = sheet.getPhysicalNumberOfRows();

                //如果Excel中没有数据则提示错误
                if(realRows<=1){
                    throw new ExcelException("Excel文件中没有任何数据");
                }

                //获得第一行
                Row firstRow = sheet.getRow(sheet.getFirstRowNum());
                List<String> excelFieldNames = new ArrayList<>();

                //获取Excel中的列名
                for(Cell cell:firstRow){
                    excelFieldNames.add(getContent(cell));
                }

                //判断需要的字段在Excel中是否都存在
                for(String cnName : fieldMap.keySet()){
                    if(!excelFieldNames.contains(cnName)){
                        throw new ExcelException("你导入的模板不正确！模板中缺少 "+cnName +" 列名称，或该列名称有误");
                    }
                }


                //将列名和列号放入Map中,这样通过列名就可以拿到列号
                LinkedHashMap<String, Integer> colMap = new LinkedHashMap<>();
                for(int i=0; i< excelFieldNames.size(); i++){
                    colMap.put(excelFieldNames.get(i), firstRow.getCell(i).getColumnIndex());
                }

                //判断是否有重复行
                //1.获取uniqueFields指定的列
                if(uniqueFields.size() > 0){
                    Cell[][] uniqueCells = new Cell[uniqueFields.size()][realRows-1];
                    for(int i=0;i<uniqueFields.size();i++){
                        int col=colMap.get(uniqueFields.get(i));
                        for(int j = 0; j < realRows-1; j++){
                            uniqueCells[i][j] = sheet.getRow(j+1).getCell(col);
                        }
                    }

                    //2.从指定列中寻找重复行
                    for(int j = 0; j < uniqueFields.size(); j++){
                        for(int i = 0; i < realRows-1; i++){
                            String currentContent = getContent(uniqueCells[j][i]);
                            for(int k = i+1; k < realRows-1; k++){
                                String content = getContent(uniqueCells[j][k]);
                                if(content.equals(currentContent)){
                                    throw new ExcelException("Excel中有重复行，请检查！重复值为："+content);
                                }
                            }
                        }
                    }
                }

                //将sheet转换为list
                for(int i=1; i<realRows; i++){
                    //新建要转换的对象
                    Map<String,String> map = new HashMap<>();

                    //给对象中的字段赋值
                    for(Map.Entry<String, String> entry : fieldMap.entrySet()){
                        //获取中文字段名
                        String cnNormalName = entry.getKey();
                        //获取英文字段名
                        String enNormalName = entry.getValue();
                        //根据中文字段名获取列号
                        int col = colMap.get(cnNormalName);

                        //获取当前单元格中的内容
                        String content = getContent(sheet.getRow(i).getCell(col));
                        //String content = sheet.getCell(col, i).getContents().toString().trim();

                        //校验不能为空的列
                        if(notNullFields.containsKey(cnNormalName)){
                            if(StringUtils.isBlank(content))
                                throw new ExcelException("Excel表的列 "+ cnNormalName +" 不能为空！请检查后重新导入。");
                        }
                        //给对象赋值
                        map.put(enNormalName, content);
                    }

                    resultList.add((T)map);
                }
            }

        } catch(Exception e){
            if(e instanceof ExcelException){
                throw new RuntimeException(e.getMessage(),e);
            }else if(e instanceof InvalidFormatException){
                throw new RuntimeException("文件格式不正确",e);
            } else {
                throw new RuntimeException("批量导入失败",e);
            }
        }finally {
            try {
                in.close();
            }catch (Exception ex){
                ex.printStackTrace();
            }
        }
        return resultList;
    }

    public static List<Worker> mapListToWorkerList(List<Map> mapList){
        List<Worker> workerList = new ArrayList<>();
        for(Map map:mapList){
            Worker worker = new Worker();
            Iterator<Map.Entry<String,String>> iterator = map.entrySet().iterator();
            while(iterator.hasNext()){
                Map.Entry<String,String> entry = iterator.next();
                try {
                    if(!"id".equals(entry.getKey())){
                        Field field = Worker.class.getDeclaredField(entry.getKey());
                        field.setAccessible(true);
                        if(field.getType() == Date.class){
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
                            Date date = sdf.parse(entry.getValue().toString());
                            field.set(worker,date);
                        }else if(field.getType() == Double.class){
                            if(StringUtils.isNotEmpty(entry.getValue())) {
                                field.set(worker, Double.valueOf(entry.getValue()));
                            }
                        }else if(field.getType() == Integer.class){
                            if(StringUtils.isNotEmpty(entry.getValue())) {
                                field.set(worker, Integer.valueOf(entry.getValue()));
                            }
                        }else if(field.getType() == Level.class){
                            if(StringUtils.isNotEmpty(entry.getValue())) {
                                field.set(worker, Level.getByDesc(entry.getValue()));
                            }
                        }else if(field.getType() == Long.class){
                            if(StringUtils.isNotEmpty(entry.getValue())) {
                                field.set(worker, Long.valueOf(entry.getValue()));
                            }
                        }else if(field.getType() == Boolean.class){
                            if(StringUtils.isNotEmpty(entry.getValue())) {
                                field.set(worker, Boolean.valueOf(entry.getValue()));
                            }
                        }else if(field.getType() == Education.class){
                            if(StringUtils.isNotEmpty(entry.getValue())) {
                                field.set(worker, Education.getByDesc(entry.getValue()));
                            }
                        }else {
                            field.set(worker,entry.getValue());
                        }
                    }else {
                        worker.setId(Long.valueOf(entry.getValue()));
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    throw new RuntimeException("批量导入失败！");
                }
            }
            workerList.add(worker);
        }
        return workerList;
    }

    private static String getContent(Cell cell){
        if(cell == null){
            return "";
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                return cell.getRichStringCellValue().getString().trim();
            case Cell.CELL_TYPE_NUMERIC:
                if(DateUtil.isCellDateFormatted(cell)){
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                }else {
                    Object inputValue = null;// 单元格值
                    Long longVal = Math.round(cell.getNumericCellValue());
                    Double doubleVal = cell.getNumericCellValue();
                    if(Double.parseDouble(longVal + ".0") == doubleVal){   //判断是否含有小数位.0
                        inputValue = longVal;
                    }
                    else{
                        inputValue = doubleVal;
                    }
                    return String.valueOf(inputValue);
                }
            case Cell.CELL_TYPE_FORMULA:
                return cell.getStringCellValue().trim();
            default:
                return "";
        }
    }

    public static class ExcelException extends Exception {
        public ExcelException(String s){
            super(s);
        }
    }

}
